# -*- coding: utf-8 -*-

import numpy as np
import pandas as pd
import string

from decimal import Decimal
from pandas.core.groupby import DataFrameGroupBy, SeriesGroupBy

"""
DataFrame类的更新函数默认都是都是不改变原对象，返回新对象
可以通过添加inplace=True参数设置在原对象上修改，返回None

函数参数名默认是index，默认修改行
如果需要修改列，需指定函数参数名为columns
或者指定函数参数名axis=0(index),1(columns)

"""


def index_method():
    n = 3
    df = pd.DataFrame(data=[[1, 2, 3], [1, 2, 2], [1, 3, np.nan]],
                      index=list(string.ascii_lowercase[:n]),
                      columns=list(string.ascii_uppercase[:n]))
    # print('df:----------\n', df, sep='')

    # DataFrame
    df1 = df.set_index(keys='B')
    df1.reset_index().rename({'index':'column_name'})


    # 设置多重索引MultiIndex
    df2 = df.set_index(keys=['B', 'C'])
    print(df2, sep='')
    # print(df2.index[0])
    # print(df2.loc[(2, 3)])

    # 重置部分索引，删除重置的列
    df3 = df2.reset_index(level='C', drop=True)
    print(df3, sep='')

    # ---------------------------------------------------------------------------------------------
    # 如果列有多个级别
    row = [('a', 'b'), ('a', 'c'), ('b', 'c')]
    col = [('A', 'B'), ('A', 'C'), ('B', 'C')]

    df = pd.DataFrame(data=[[1, 2, 3], [1, 2, np.nan], [1, np.nan, 3]],
                      index=pd.MultiIndex.from_tuples(tuples=row, names=['I', 'II']),
                      columns=pd.MultiIndex.from_tuples(tuples=col))
    print('df:----------\n', df, sep='')

    # 多级别列重置部分索引
    df1 = df.reset_index(level='II', col_level=1, col_fill='XX')
    print('df:----------\n', df1, sep='')

    pass


def sort_method():
    n = 3
    df = pd.DataFrame(data=[[1, 2, 3], [1, 2, 2], [1, 3, np.nan]],
                      index=list(string.ascii_lowercase[:n]),
                      columns=list(string.ascii_uppercase[:n]))
    # 按指定的index排序
    df1 = df.sort_index(axis=0)
    # 按指定的columns排序
    df2 = df.copy()
    df2.sort_values(by=['A'], inplace=True)
    print(df2)

    pass


def drop_method():
    """

    :return:
    """

    # labels：要删除的行或列，删除多行或列可以使用列表作为参数
    # axis：默认为0表示要删除的是行，当axis为1表示要删除的是列
    # index：直接指定要删除的行，删除多行可以使用列表作为参数
    # columns：直接指定要删除的列，删除多列可以使用列表作为参数
    n = 10
    df = pd.DataFrame(data=np.arange(0, n * n, 1).reshape(n, n),
                      index=list(string.ascii_lowercase[:n]),
                      columns=list(string.ascii_uppercase[:n]))

    # 删除行或列
    # 1. 通过参数labels和axis来指定
    # 2. 通过参数index或者columns来指定

    # 删除行
    df1 = df.drop(labels='a', axis=0)
    df1 = df.drop(labels=['a', 'b'], axis=0)

    df1 = df.drop(index='a')
    df1 = df.drop(index=['a', 'b'])

    print('df1:----------\n', df1, sep='')

    # 删除列
    df2 = df.drop(labels='A', axis=1)
    df2 = df.drop(labels=['A', 'B'], axis=1)

    df2 = df.drop(columns='A')
    df2 = df.drop(columns=['A', 'B'])
    df.drop_duplicates()

    print('df2:----------\n', df2, sep='')

    # 使用关键字del删除，改变原值df
    del df['A']
    print('df:----------\n', df, sep='')

    # 删除存在np.nan的行或列
    df.dropna(inplace=True)
    df = df.dropna(axis=1)

    pass


def shift_method():
    """
    对列进行向上或下移位，实现错位相加或减操作
    :return:
    """
    n = 10
    df = pd.DataFrame(data=np.arange(0, n * n, 1).reshape(n, n),
                      index=list(string.ascii_lowercase[:n]),
                      columns=list(string.ascii_uppercase[:n]))
    # print('df:----------\n', df, sep='')
    # periods：默认是1，表示向下移动一位
    # df['A+1'] = df['A'].shift(periods=1) # Series
    # print(df[['A', 'A+1']], sep='')

    # freq：频率
    # axis：0-index，1-columns
    # 按行移位
    df1 = df.shift(periods=2, axis=0)
    print(df1, sep='')

    # fill_value：填充值，替换值

    # 按列移位，填充偷换NaN为0
    df2 = df.shift(periods=2, axis=1, fill_value=0.0)
    print(df2, sep='')

    pass


def apply_method():
    """

    series.apply(lambda x:x)
    df.apply(lambda col:col, axis=0)
    df.apply(lambda row:row, axis=1)
    df.applymap(lambda x:x)


    apply函数没有inplace参数

    apply_function必须有返回值


    """
    n = 5
    index = pd.Index(data=range(n))
    print('index =', index.values)

    def apply_function(x):
        return x ** 2

    series = pd.Series(data=np.arange(n), index=index)
    print('series =', series.values)

    print(series.apply(func=apply_function).values)
    print(series.apply(lambda x: x ** 2).values)

    df = pd.DataFrame(data=np.arange(0, n ** 2).reshape(n, n),
                      index=index, columns=list(string.ascii_uppercase[:n]))
    print('df:----------\n', df, sep='')
    print('df.apply0:----------\n', df.apply(lambda x: x[2] ** 2, axis=0).values, sep='')
    print('df.apply1:----------\n', df.apply(lambda x: x['C'] ** 2, axis=1).values, sep='')

    pass


def group_method():
    df = pd.DataFrame(data=[['111', '222', 3], ['111', '444', 5], ['666', '555', 7]],
                      index=np.arange(3), columns=list(string.ascii_uppercase[:3]))
    print('df:----------\n', df, sep='')

    """
    SeriesGroupBy.agg('agg_func')
    SeriesGroupBy.agg_func()
    
    可以单独使用聚合函数
    DataFrameGroupBy.agg({'col_name':'agg_func'})
    可以多列使用聚合函数
    
    
    聚合函数 agg=aggregate
    
    min
    max
    sum
    mean
    count

    
    df新增agg列
    df.transform('agg_func')
    
    
    """
    # DataFrameGroupBy = DataFrame.groupby(by='col_name')
    # DataFrameGroupBy = DataFrame.groupby(by=['col_name_1', 'col_name_2'])
    df_gb: DataFrameGroupBy = df.groupby(by='A')
    # 遍历
    for name, group in df_gb:
        print(name, group)
        pass
    print(df_gb['B'].max())
    # SeriesGroupBy = Series.groupby(by=Series)
    series_gb: SeriesGroupBy = df['B'].groupby(by=df['A'])

    print(series_gb.min())

    # SeriesGroupBy.unique()
    # DataFrameGroupBy.nunique()
    print(df_gb.nunique())

    gb_s1 = df_gb['C']  # SeriesGroupBy

    # #
    # df['D'] = df_gb.transform('sum')
    #
    # s1 = gb_s1.unique()  # Series
    # df1 = s1.reset_index()  # DataFrame
    # df1['B'] = df1['B'].apply(lambda x: ','.join(x))
    # print('df:----------\n', df1, sep='')
    #
    # # 分组之后是SeriesGroupBy，再转成DataFrame
    # df2 = df1.unique().reset_index()
    # # print(type(df2))
    # df2['B'] = df2['B'].apply(lambda x: ';'.join(x))
    # print('df:----------\n', df2, sep='')

    # def aggregate(l):
    #     '''自定义的聚合函数'''
    #     # 平方求和
    #     return np.sum(np.power(l, 2))

    # df3 = df['C'].groupby([df['A'], df['B']]).apply(aggregate)
    # print('df:----------\n', df3, sep='')
    # # 只有按多列groupby时才能使用unstack
    # df4 = df3.unstack()
    # print('d:----------\n', df4, sep='')
    #

    # 聚合字段存在nan，None等值，此列不会统计
    # df = pd.DataFrame(data=[['111', Decimal('2.3'), 3], ['111', np.nan, 5], ['666', Decimal('4.7'), 7]],
    #                   index=[1, 2, 3], columns=['A', 'B', 'C'])
    # # df = pd.DataFrame(data=[['111', 2, 3], ['111', np.nan, 5], ['666', 4.7, 7]],
    # #                   index=[1, 2, 3], columns=['A', 'B', 'C'])
    # df['B'] = df['B'].fillna(Decimal('0.0'))
    # df['B'] = df['B'].astype(str).apply(Decimal)
    # gb: DataFrameGroupBy = df.groupby(by='A')

    # gb_sum = gb[['B', 'C']].sum()

    # 分组字段存在None等值，此行被忽略统计
    df = pd.DataFrame(data=[['111', 'AAA', 3], ['111', None, 4], ['666', 'CCC', 3]],
                      index=[1, 2, 3], columns=['A', 'B', 'C'])
    all_sum = df['C'].sum()
    gb = df.groupby(by=['A', 'B'], dropna=False)['C']
    for key, agg in gb:
        print(key, agg.sum())

    group_list = [['A'], ['A', 'B']]
    for group_columns in group_list:
        for group_index, data_series in df.groupby(by=group_columns):
            key_str = group_index if isinstance(group_index, str) else ','.join(list(group_index))
            print(key_str)

    # for gb_a, df_a in df.groupby(by='A'):
    #     for gb_b, df_b in df_a.groupby(by='B'):
    #         one_sum = df_b['C'].sum()
    #         print(gb_a, gb_b, one_sum, one_sum / all_sum)

    pass


if __name__ == '__main__':
    """
    DataFrame对象函数
    """
    index_method()
    # apply_method()
    # group_method()

    # sort_method()
    # drop_method()

    pass
